import os

from flask import Blueprint, request, jsonify, send_file
from flask_cors import CORS
from openpyxl import load_workbook
import pandas as pd
import shutil  # 用于复制文件
from src.main.dao.StudentDao import StudentDao
from src.main.domain.DataConverter import DataConverter
from src.main.domain.student.Student import Student
from src.main.util.ApplicationConfig import ApplicationConfig

student_blueprint = Blueprint('student', __name__)
CORS(student_blueprint)

# 查询学生信息
@student_blueprint.route('/getStudents', methods=['POST'])
def get_students():
    dao = StudentDao()
    dao.connect()
    pageNumber = request.json.get('pageNumber')
    pageSize = request.json.get('pageSize')
    filter_gender = request.json.get('filterGender')
    filter_keyword = request.json.get('filterKeyword')
    results = dao.get_student_info_by_page(pageNumber, pageSize,filter_gender,filter_keyword)
    students = DataConverter.convert_results_to_objects(results, Student)
    data = DataConverter.convert_objects_to_json(students)
    response = {'success': True, 'data': data}
    return jsonify(response)

# 新增或更新学生信息
@student_blueprint.route('/saveStudent', methods=['POST'])
def save_student():
    dao = StudentDao()
    dao.connect()
    id = request.json.get('id', 0)  # 默认值设为0，以便后续判断是否为更新操作
    name = request.json.get('name')
    age = request.json.get('age')
    grade = request.json.get('grade')
    gender = request.json.get('gender')
    email = request.json.get('email')
    phone = request.json.get('phone')
    address = request.json.get('address')
    enrollment_date = request.json.get('enrollment_date')

    if id > 0:
        row_count = dao.update_student(id, name, age, grade, gender, email, phone, address, enrollment_date)
    else:
        row_count = dao.insert_student(name, age, grade, gender, email, phone, address, enrollment_date)

    success = row_count > 0  # 根据受影响的行数判断操作是否成功

    response = {'success': success}
    return jsonify(response)


@student_blueprint.route('/import', methods=['POST'])
def import_students_from_excel():
    print(request.files)
    dao = StudentDao()
    dao.connect()
    files = request.files.getlist('file')  # 从请求中获取上传的 Excel 文件

    if not files:
        response = {'success': False, 'message': '没有上传 Excel 文件'}
        return jsonify(response)

    total_row_count = 0  # 记录总共插入/更新的行数

    for excel_file in files:
        try:
            # 读取 Excel 文件内容
            df = pd.read_excel(excel_file)

            # 遍历 Excel 表格的每一行
            for index, row in df.iterrows():
                name = row.get('姓名')
                age = row.get('年龄')
                grade = row.get('年级')
                gender = row.get('性别')
                email = row.get('邮箱')
                phone = row.get('手机号码')
                address = row.get('住址')
                enrollment_date = row.get('入学时间')

                if pd.isna(enrollment_date):  # 如果入学时间为 NaN，则赋值为 None
                    enrollment_date = None

                row_count = dao.insert_student(name, age, grade, gender, email, phone, address, enrollment_date)

                total_row_count += row_count  # 更新总行数统计

            response = {'code': '200', 'message': total_row_count}
        except Exception as e:
            response = {'code': '201', 'message': str(e)}
            break  # 出现异常时直接退出循环

    dao.close()  # 关闭数据库连接
    return jsonify(response)



@student_blueprint.route('/export', methods=['GET'])
def export_students_to_excel():
    dao = StudentDao()
    dao.connect()

    try:
        # 从数据库中获取学生信息
        results = dao.get_all_student_info()
        students = DataConverter.convert_results_to_objects(results, Student)
        print(students)

        # 复制模板文件
        app_config = ApplicationConfig()
        template_file_path = app_config.get_root_dir() + '/src/resources/学生信息模板文件.xlsx'
        excel_file_path = '学生信息.xlsx'
        shutil.copyfile(template_file_path, excel_file_path)

        # 使用 openpyxl 加载原始 Excel 文件
        wb = load_workbook(excel_file_path)
        ws = wb.active

        # 将数据从第二行开始写入 Excel 文件
        for idx, row_data in enumerate(results, start=2):
            ws.append(row_data)

        # 保存修改后的 Excel 文件
        wb.save(excel_file_path)

        # 返回 Excel 文件给客户端
        response =  send_file(excel_file_path, as_attachment=True)
        # 删除生成的临时文件
        if os.path.exists(excel_file_path):
            os.remove(excel_file_path)
        return response
    except Exception as e:
        response = {'code': '201', 'message': str(e)}
        return jsonify(response)
    finally:
        dao.close()  # 关闭数据库连接


# 删除学生信息
@student_blueprint.route('/deleteStudent', methods=['GET'])  # 改为 GET 方法
def delete_student():
    dao = StudentDao()
    dao.connect()
    id = request.args.get('id', 0)  # 从 URL 中获取学生的 ID

    if id:
        id = int(id)  # 将 id 转换为整数
        row_count = dao.delete_student(id)

    success = row_count > 0  # 根据受影响的行数判断操作是否成功

    response = {'success': success}
    return jsonify(response)